﻿Public Class ExportaToExcel
    Private lDt As New DataTable
    Private lDatosXml As System.Text.StringBuilder
    Private Function GetEncabezadoXML() As String
        Try
            Dim EncaXml As String = "<?xml version=""1.0""?>" & _
                                    "<?mso-application progid=""Excel.Sheet""?>" & _
                                    "<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""" & _
                                    "xmlns:o = ""urn:schemas-microsoft-com:office:office""" & _
                                    "xmlns :x = ""urn:schemas-microsoft-com:office:excel""" & _
                                    "xmlns :ss = ""urn:schemas-microsoft-com:office:spreadsheet""" & _
                                    "xmlns:html=""http://www.w3.org/TR/REC-html40"">" & _
                                    "<DocumentProperties xmlns=""urn:schemas-microsoft-com:office:office"">" & _
                                    "<Author>sgomez</Author>" & _
                                    "<LastAuthor>sgomez</LastAuthor>" & _
                                    "<Created>" & Date.Now & "</Created>" & _
                                    "<Version>12.00</Version>" & _
                                    "</DocumentProperties>" & _
                                    "<ExcelWorkbook xmlns=""urn:schemas-microsoft-com:office:excel"">" & _
                                    "<WindowHeight>9780</WindowHeight>" & _
                                    "<WindowWidth>21315</WindowWidth>" & _
                                    "<WindowTopX>120</WindowTopX>" & _
                                    "<WindowTopY>135</WindowTopY>" & _
                                    "<ProtectStructure>False</ProtectStructure>" & _
                                    "<ProtectWindows>False</ProtectWindows>" & _
                                    "</ExcelWorkbook>" & _
                                    "<Styles>" & _
                                    "<Style ss:ID=""Default"" ss:Name=""Normal"">" & _
                                    "<Alignment ss:Vertical=""Bottom""/>" & _
                                    "<Borders/>" & _
                                    "<Font ss:FontName=""Calibri"" x:Family=""Swiss"" ss:Size=""11"" ss:Color=""#000000""/>" & _
                                    "<Interior/>" & _
                                    "<NumberFormat/>" & _
                                    "<Protection/>" & _
                                    "</Style>" & _
                                    "<Style ss:ID=""s62"">" & _
                                    "<Font ss:FontName=""Calibri"" x:Family=""Swiss"" ss:Size=""11"" ss:Color=""#000000""" & _
                                    "ss:Bold=""1""/>" & _
                                    "</Style>" & _
                                    "<Style ss:ID=""s63"">" & _
                                    "<NumberFormat ss:Format=""Short Date""/>" & _
                                    "</Style>" & _
                                    "<Style ss:ID=""s64"">" & _
                                    "<NumberFormat ss:Format=""&quot;$&quot;\ #,##0.00""/>" & _
                                    "</Style>" & _
                                    "</Styles>"
            Return EncaXml
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function GetEstilo(ByVal TipoEstilo As String) As String
        Select Case TipoEstilo
            Case "Texto"
                Return "ss:StyleID=""s62"""
            Case "Fecha"
                Return "ss:StyleID=""s63"""
            Case "Moneda"
                Return "ss:StyleID=""s64"""
            Case Else
                Return ""
        End Select
    End Function
    Private Function GetTipo(ByVal Tipo As String) As String
        Select Case Tipo
            Case "Texto"
                Return "ss:Type=""String"""
            Case "Numero"
                Return "ss:Type=""Number"""
            Case "Fecha"
                Return "ss:Type=""DateTime"""
            Case Else
                Return ""
        End Select
    End Function
    Public Function GetHoja(ByVal Nombre As String) As String
        Dim HojaXml As String = "<Worksheet ss:Name=" & Nombre & ">" & _
                                "<Table ss:ExpandedColumnCount=""12"" ss:ExpandedRowCount=""15"" x:FullColumns=""1""" & _
                                "x:FullRows=""1"" ss:DefaultColumnWidth=""60"" ss:DefaultRowHeight=""15"">" & _
                                "<Column ss:Index=""2"" ss:Width=""60.75"" ss:Span=""9""/>" & _
                                "<Column ss:Index=""12"" ss:Width=""74.25""/>"
        Return HojaXml
    End Function
    Public Function Export() As String
        Dim IniRow As String = "<Row ss:AutoFitHeight=""0"">"
        Dim FinRow As String = "</Row>"
        Dim FinCell As String = "</Cell>"
        Dim FinData As String = "</Data>"
        lDatosXml.Append(IniRow)
        For Each col As DataColumn In lDt.Columns
            lDatosXml.Append("<Cell " + GetEstilo("Texto") + "> <Data " + GetTipo("Texto") + ">" + col.ColumnName + FinData + FinCell)
        Next
        lDatosXml.Append(FinRow)


    End Function
    Public Sub New(ByVal dt As DataTable)
        
        lDt = dt
        lDatosXml = New System.Text.StringBuilder
    End Sub
End Class
